<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Item_request_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function get_items(){
        $sql = "SELECT * FROM inv_items where enable_flag = 'Y' ";
       // $sql="SELECT urs.line_id line_id, urs.rate_id rate_id, uce.charge_name charge_name, urs.description description, urs.usage_qty usage_qty, urs.rate rate FROM ubs_rate_schedule urs INNER JOIN ubs_chg_elements uce ON uce.charge_code = urs.charge_code WHERE urs.rate_id = '".$id."' ";
        $query = $this->db->query($sql);
        return $query->result();
    
    }

    public function get_inv_access($id){
        $sql = "SELECT * FROM inv_officer_access WHERE user_id = ".$id." ";
        $query = $this->db->query($sql);
        return $query->result();

    }

    public function get_reqNum($val){
        $this->db->query("call get_request_num(?,@val)", $val);
        $que = $this->db->query("SELECT @val as val");
        return $que->result();
    }

    public function get_inv_users($id,$role){
      $and;
      if($role == 'REQ'){ $and = " AND io.role_code = 'REQ_OFFCR' ";}
      if($role == 'APR'){ $and = " AND io.role_code = 'APRV_OFFCR' ";}
      if($role == 'RLS'){ $and = " AND io.role_code = 'RLS_OFFCR' ";}
      $sql    = "SELECT io.* , au.user_name , au.last_name , au.first_name FROM `inv_officers` io
                    INNER JOIN app_users au on au.user_id = io.user_id
                WHERE au.company_id = ".$id." ".$and." AND au.enable_flag = 'Y' ";
      $query  = $this->db->query($sql);
      return $query->result();
    }

    public function get_status(){
      $sql   = "SELECT * FROM `app_lookup_values` WHERE lookup_type = 'INV_REQ_STAT'";
      $query = $this->db->query($sql);
      return $query->result();
    }


    public function insert_req_data($data){
        $this->db->query("call inv_req_item_ins(?,?,?,?,?,?,?,?,@id,@err,@msg)", $data);
        $que = $this->db->query("SELECT @id as id , @err as error , @msg as msg");
        echo json_encode($que->result());
    }

    public function update_req_data($data){
        $this->db->query("call inv_item_request_upd(?,?,?,?,?,@err,@msg)", $data);
        $que = $this->db->query("SELECT @err as error , @msg as msg");
        echo json_encode($que->result());
    }

    public function insert_content($data){
        $this->db->query("call inv_reqitem_content_ins(?,?,?,?,?,@id,@err,@msg)", $data);
        $que = $this->db->query("SELECT @id as id , @err as error , @msg as msg");
        return $que->result();
    }


    public function get_contents($id){
      $sql   = "SELECT iic.* , ii.item_num , ii.description FROM inv_itemreq_content iic 
                    INNER JOIN inv_items ii ON ii.item_id = iic.item_id 
                WHERE iic.inv_request_id = ".$id." ";
      $query = $this->db->query($sql);
      return $query->result();
    }

    public function get_req_datus($id){
      $sql   = "SELECT iir.*, alv.meaning , au.first_name , au.last_name, au.user_name , au.user_id  FROM `inv_item_request` iir
                      INNER JOIN app_lookup_values alv ON alv.lookup_code = iir.inv_request_status
                      INNER JOIN app_users au ON au.user_id = iir.inv_requested_by
                WHERE alv.lookup_type = 'INV_REQ_STAT' AND iir.inv_request_id = ".$id."  ";
      $query = $this->db->query($sql);
      return $query->result();
    }

    public function approve_request($data){
        $this->db->query("call inv_item_request_apprv(?,?,?,@err,@msg)", $data);
        $que = $this->db->query("SELECT @err as error , @msg as msg");
        echo json_encode($que->result());
    }

    public function delete_req_contents($data){
        $this->db->query("call inv_itemreq_content_del(?,@err,@msg)",$data);
        $que = $this->db->query("SELECT @err as error , @msg as msg");
        return $que->result();
    }

    public function search_request($reqNum, $status){
      $where = "";
      

          if($reqNum != "" && $status != ""){
            $where = "AND iir.inv_request_num LIKE ".$this->db->escape($reqNum)." AND iir.inv_request_status LIKE '".$status."' ";
          }else if($reqNum == "" && $status != ""){
            $where = "AND iir.inv_request_status LIKE ".$this->db->escape($status)." ";
          }else{
            $where = "AND iir.inv_request_num LIKE ".$this->db->escape($reqNum)." ";
          }

          $sql = "SELECT iir.*, alv.meaning , au.first_name , au.last_name  FROM `inv_item_request` iir
                        INNER JOIN app_lookup_values alv ON alv.lookup_code = iir.inv_request_status
                        INNER JOIN app_users au ON au.user_id = iir.inv_requested_by
                  WHERE alv.lookup_type = 'INV_REQ_STAT' ".$where." ";
          //$query = $this->db->get_where('inv_uom', array('module_code' => $modulecode, 'module_name' => $modulename), 1);
          $query = $this->db->query($sql);
          if($query->num_rows() != 0){
            return $query->result();
          }
          else {
            $result = "No results found.";
            return $result;
          }
    }
}